/*DONE* Table [dbo].[<CompanyName>$Purchase Line$437dbf0e-84ff-417a-965d-ed2bb9650972]  1/2  Script Date: 29.7.2020 - JI ******/
/*Add prestaging table for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT MAX([Company]) FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Purchase Line$437dbf0e-84ff-417a-965d-ed2bb9650972]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Purchase Line$437dbf0e-84ff-417a-965d-ed2bb9650972](
	[timestamp] [timestamp] NOT NULL,
	[Document Type] [int] NOT NULL,
	[Document No_] [nvarchar](20) NOT NULL,
	[Line No_] [int] NOT NULL,
	[Buy-from Vendor No_] [nvarchar](20) NOT NULL,
	[Type] [int] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Location Code] [nvarchar](10) NOT NULL,
	[Posting Group] [nvarchar](20) NOT NULL,
	[Expected Receipt Date] [datetime] NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Description 2] [nvarchar](50) NOT NULL,
	[Unit of Measure] [nvarchar](50) NOT NULL,
	[Quantity] [decimal](38, 20) NOT NULL,
	[Outstanding Quantity] [decimal](38, 20) NOT NULL,
	[Qty_ to Invoice] [decimal](38, 20) NOT NULL,
	[Qty_ to Receive] [decimal](38, 20) NOT NULL,
	[Direct Unit Cost] [decimal](38, 20) NOT NULL,
	[Unit Cost (LCY)] [decimal](38, 20) NOT NULL,
	[VAT _] [decimal](38, 20) NOT NULL,
	[Line Discount _] [decimal](38, 20) NOT NULL,
	[Line Discount Amount] [decimal](38, 20) NOT NULL,
	[Amount] [decimal](38, 20) NOT NULL,
	[Amount Including VAT] [decimal](38, 20) NOT NULL,
	[Unit Price (LCY)] [decimal](38, 20) NOT NULL,
	[Allow Invoice Disc_] [tinyint] NOT NULL,
	[Gross Weight] [decimal](38, 20) NOT NULL,
	[Net Weight] [decimal](38, 20) NOT NULL,
	[Units per Parcel] [decimal](38, 20) NOT NULL,
	[Unit Volume] [decimal](38, 20) NOT NULL,
	[Appl_-to Item Entry] [int] NOT NULL,
	[Shortcut Dimension 1 Code] [nvarchar](20) NOT NULL,
	[Shortcut Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Job No_] [nvarchar](20) NOT NULL,
	[Indirect Cost _] [decimal](38, 20) NOT NULL,
	[Recalculate Invoice Disc_] [tinyint] NOT NULL,
	[Outstanding Amount] [decimal](38, 20) NOT NULL,
	[Qty_ Rcd_ Not Invoiced] [decimal](38, 20) NOT NULL,
	[Amt_ Rcd_ Not Invoiced] [decimal](38, 20) NOT NULL,
	[Quantity Received] [decimal](38, 20) NOT NULL,
	[Quantity Invoiced] [decimal](38, 20) NOT NULL,
	[Receipt No_] [nvarchar](20) NOT NULL,
	[Receipt Line No_] [int] NOT NULL,
	[Order No_] [nvarchar](20) NOT NULL,
	[Order Line No_] [int] NOT NULL,
	[Profit _] [decimal](38, 20) NOT NULL,
	[Pay-to Vendor No_] [nvarchar](20) NOT NULL,
	[Inv_ Discount Amount] [decimal](38, 20) NOT NULL,
	[Vendor Item No_] [nvarchar](50) NOT NULL,
	[Sales Order No_] [nvarchar](20) NOT NULL,
	[Sales Order Line No_] [int] NOT NULL,
	[Drop Shipment] [tinyint] NOT NULL,
	[Gen_ Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[Gen_ Prod_ Posting Group] [nvarchar](20) NOT NULL,
	[VAT Calculation Type] [int] NOT NULL,
	[Transaction Type] [nvarchar](10) NOT NULL,
	[Transport Method] [nvarchar](10) NOT NULL,
	[Attached to Line No_] [int] NOT NULL,
	[Entry Point] [nvarchar](10) NOT NULL,
	[Area] [nvarchar](10) NOT NULL,
	[Transaction Specification] [nvarchar](10) NOT NULL,
	[Tax Area Code] [nvarchar](20) NOT NULL,
	[Tax Liable] [tinyint] NOT NULL,
	[Tax Group Code] [nvarchar](20) NOT NULL,
	[Use Tax] [tinyint] NOT NULL,
	[VAT Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[VAT Prod_ Posting Group] [nvarchar](20) NOT NULL,
	[Currency Code] [nvarchar](10) NOT NULL,
	[Outstanding Amount (LCY)] [decimal](38, 20) NOT NULL,
	[Amt_ Rcd_ Not Invoiced (LCY)] [decimal](38, 20) NOT NULL,
	[Blanket Order No_] [nvarchar](20) NOT NULL,
	[Blanket Order Line No_] [int] NOT NULL,
	[VAT Base Amount] [decimal](38, 20) NOT NULL,
	[Unit Cost] [decimal](38, 20) NOT NULL,
	[System-Created Entry] [tinyint] NOT NULL,
	[Line Amount] [decimal](38, 20) NOT NULL,
	[VAT Difference] [decimal](38, 20) NOT NULL,
	[Inv_ Disc_ Amount to Invoice] [decimal](38, 20) NOT NULL,
	[VAT Identifier] [nvarchar](20) NOT NULL,
	[IC Partner Ref_ Type] [int] NOT NULL,
	[IC Partner Reference] [nvarchar](20) NOT NULL,
	[Prepayment _] [decimal](38, 20) NOT NULL,
	[Prepmt_ Line Amount] [decimal](38, 20) NOT NULL,
	[Prepmt_ Amt_ Inv_] [decimal](38, 20) NOT NULL,
	[Prepmt_ Amt_ Incl_ VAT] [decimal](38, 20) NOT NULL,
	[Prepayment Amount] [decimal](38, 20) NOT NULL,
	[Prepmt_ VAT Base Amt_] [decimal](38, 20) NOT NULL,
	[Prepayment VAT _] [decimal](38, 20) NOT NULL,
	[Prepmt_ VAT Calc_ Type] [int] NOT NULL,
	[Prepayment VAT Identifier] [nvarchar](20) NOT NULL,
	[Prepayment Tax Area Code] [nvarchar](20) NOT NULL,
	[Prepayment Tax Liable] [tinyint] NOT NULL,
	[Prepayment Tax Group Code] [nvarchar](20) NOT NULL,
	[Prepmt Amt to Deduct] [decimal](38, 20) NOT NULL,
	[Prepmt Amt Deducted] [decimal](38, 20) NOT NULL,
	[Prepayment Line] [tinyint] NOT NULL,
	[Prepmt_ Amount Inv_ Incl_ VAT] [decimal](38, 20) NOT NULL,
	[Prepmt_ Amount Inv_ (LCY)] [decimal](38, 20) NOT NULL,
	[IC Partner Code] [nvarchar](20) NOT NULL,
	[Prepmt_ VAT Amount Inv_ (LCY)] [decimal](38, 20) NOT NULL,
	[Prepayment VAT Difference] [decimal](38, 20) NOT NULL,
	[Prepmt VAT Diff_ to Deduct] [decimal](38, 20) NOT NULL,
	[Prepmt VAT Diff_ Deducted] [decimal](38, 20) NOT NULL,
	[IC Item Reference No_] [nvarchar](50) NOT NULL,
	[Outstanding Amt_ Ex_ VAT (LCY)] [decimal](38, 20) NOT NULL,
	[A_ Rcd_ Not Inv_ Ex_ VAT (LCY)] [decimal](38, 20) NOT NULL,
	[Pmt_ Discount Amount] [decimal](38, 20) NOT NULL,
	[Dimension Set ID] [int] NOT NULL,
	[Job Task No_] [nvarchar](20) NOT NULL,
	[Job Line Type] [int] NOT NULL,
	[Job Unit Price] [decimal](38, 20) NOT NULL,
	[Job Total Price] [decimal](38, 20) NOT NULL,
	[Job Line Amount] [decimal](38, 20) NOT NULL,
	[Job Line Discount Amount] [decimal](38, 20) NOT NULL,
	[Job Line Discount _] [decimal](38, 20) NOT NULL,
	[Job Unit Price (LCY)] [decimal](38, 20) NOT NULL,
	[Job Total Price (LCY)] [decimal](38, 20) NOT NULL,
	[Job Line Amount (LCY)] [decimal](38, 20) NOT NULL,
	[Job Line Disc_ Amount (LCY)] [decimal](38, 20) NOT NULL,
	[Job Currency Factor] [decimal](38, 20) NOT NULL,
	[Job Currency Code] [nvarchar](20) NOT NULL,
	[Job Planning Line No_] [int] NOT NULL,
	[Job Remaining Qty_] [decimal](38, 20) NOT NULL,
	[Job Remaining Qty_ (Base)] [decimal](38, 20) NOT NULL,
	[Deferral Code] [nvarchar](10) NOT NULL,
	[Returns Deferral Start Date] [datetime] NOT NULL,
	[Prod_ Order No_] [nvarchar](20) NOT NULL,
	[Variant Code] [nvarchar](10) NOT NULL,
	[Bin Code] [nvarchar](20) NOT NULL,
	[Qty_ per Unit of Measure] [decimal](38, 20) NOT NULL,
	[Unit of Measure Code] [nvarchar](10) NOT NULL,
	[Quantity (Base)] [decimal](38, 20) NOT NULL,
	[Outstanding Qty_ (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ to Invoice (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ to Receive (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ Rcd_ Not Invoiced (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ Received (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ Invoiced (Base)] [decimal](38, 20) NOT NULL,
	[FA Posting Date] [datetime] NOT NULL,
	[FA Posting Type] [int] NOT NULL,
	[Depreciation Book Code] [nvarchar](10) NOT NULL,
	[Salvage Value] [decimal](38, 20) NOT NULL,
	[Depr_ until FA Posting Date] [tinyint] NOT NULL,
	[Depr_ Acquisition Cost] [tinyint] NOT NULL,
	[Maintenance Code] [nvarchar](10) NOT NULL,
	[Insurance No_] [nvarchar](20) NOT NULL,
	[Budgeted FA No_] [nvarchar](20) NOT NULL,
	[Duplicate in Depreciation Book] [nvarchar](10) NOT NULL,
	[Use Duplication List] [tinyint] NOT NULL,
	[Responsibility Center] [nvarchar](10) NOT NULL,
	[Cross-Reference No_] [nvarchar](20) NOT NULL,
	[Unit of Measure (Cross Ref_)] [nvarchar](10) NOT NULL,
	[Cross-Reference Type] [int] NOT NULL,
	[Cross-Reference Type No_] [nvarchar](30) NOT NULL,
	[Item Category Code] [nvarchar](20) NOT NULL,
	[Nonstock] [tinyint] NOT NULL,
	[Purchasing Code] [nvarchar](10) NOT NULL,
	[Product Group Code] [nvarchar](10) NOT NULL,
	[Special Order] [tinyint] NOT NULL,
	[Special Order Sales No_] [nvarchar](20) NOT NULL,
	[Special Order Sales Line No_] [int] NOT NULL,
	[Item Reference No_] [nvarchar](50) NOT NULL,
	[Item Reference Unit of Measure] [nvarchar](10) NOT NULL,
	[Item Reference Type] [int] NOT NULL,
	[Item Reference Type No_] [nvarchar](30) NOT NULL,
	[Completely Received] [tinyint] NOT NULL,
	[Requested Receipt Date] [datetime] NOT NULL,
	[Promised Receipt Date] [datetime] NOT NULL,
	[Lead Time Calculation] [varchar](32) NOT NULL,
	[Inbound Whse_ Handling Time] [varchar](32) NOT NULL,
	[Planned Receipt Date] [datetime] NOT NULL,
	[Order Date] [datetime] NOT NULL,
	[Allow Item Charge Assignment] [tinyint] NOT NULL,
	[Return Qty_ to Ship] [decimal](38, 20) NOT NULL,
	[Return Qty_ to Ship (Base)] [decimal](38, 20) NOT NULL,
	[Return Qty_ Shipped Not Invd_] [decimal](38, 20) NOT NULL,
	[Ret_ Qty_ Shpd Not Invd_(Base)] [decimal](38, 20) NOT NULL,
	[Return Shpd_ Not Invd_] [decimal](38, 20) NOT NULL,
	[Return Shpd_ Not Invd_ (LCY)] [decimal](38, 20) NOT NULL,
	[Return Qty_ Shipped] [decimal](38, 20) NOT NULL,
	[Return Qty_ Shipped (Base)] [decimal](38, 20) NOT NULL,
	[Return Shipment No_] [nvarchar](20) NOT NULL,
	[Return Shipment Line No_] [int] NOT NULL,
	[Return Reason Code] [nvarchar](10) NOT NULL,
	[Subtype] [int] NOT NULL,
	[Copied From Posted Doc_] [tinyint] NOT NULL,
	[Price Calculation Method] [int] NOT NULL,
	[Over-Receipt Quantity] [decimal](38, 20) NOT NULL,
	[Over-Receipt Code] [nvarchar](20) NOT NULL,
	[Over-Receipt Approval Status] [int] NOT NULL,
	[Routing No_] [nvarchar](20) NOT NULL,
	[Operation No_] [nvarchar](10) NOT NULL,
	[Work Center No_] [nvarchar](20) NOT NULL,
	[Finished] [tinyint] NOT NULL,
	[Prod_ Order Line No_] [int] NOT NULL,
	[Overhead Rate] [decimal](38, 20) NOT NULL,
	[MPS Order] [tinyint] NOT NULL,
	[Planning Flexibility] [int] NOT NULL,
	[Safety Lead Time] [varchar](32) NOT NULL,
	[Routing Reference No_] [int] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
 CONSTRAINT ['+@Company+'$Purchase Line$437dbf0e-84ff-417a-965d-ed2bb9650972$Key1] PRIMARY KEY CLUSTERED 
(
	[Document Type] ASC,
	[Document No_] ASC,
	[Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Purchase Line$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i+1
END